package z.t.b.db.compare.dml;

import org.apache.commons.lang3.StringUtils;
import z.t.b.db.compare.DBCompare;
import z.t.b.db.compare.TableParam;
import z.t.b.db.compare.report.CompareReporter;
import z.t.b.db.data.RowData;
import z.t.b.db.data.TableData;
import z.t.b.db.meta.Column;
import z.t.b.db.meta.DB;
import z.t.b.db.meta.Index;
import z.t.b.db.meta.Table;
import z.t.b.db.utils.StrUtils;

import java.sql.Blob;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @author ：ztb
 * @date ：Created in 2024/3/27 19:35
 * @description：dml对比
 * @modified By：ztb
 * @version: 1.0
 */
public class DMLCompare implements DBCompare {

    /**
     * 源
     */
    private DB source;
    /**
     * 目标
     */
    private DB target;
    /**
     * 带比较对象
     */
    private List<TableParam> toCompareList;
    /**
     * 输出报告
     */
    private CompareReporter<String> reporter;

    public DMLCompare(DB source, DB target, CompareReporter reporter) {
        this.source = source;
        this.target = target;
        this.reporter = reporter;
    }

    @Override
    public void compareTables(List<TableParam> tableList) {
        if(tableList == null || tableList.size() == 0){
            return;
        }
        for(TableParam tableParam : tableList){
            compareSingleTable(tableParam);
        }
    }

    /**
     *
     * @param tableParam
     */
    public void compareSingleTable(TableParam tableParam){
        TableData srcTable = initTableData(tableParam);
        TableData tarTable = srcTable.copy();
        /**
         * 加载数据
         */
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT DISTINCT ");
        sql.append(srcTable.getColumnList().stream().collect(Collectors.joining(",")));
        sql.append(" FROM ").append(srcTable.getTableName());
        sql.append(" WHERE");
        String condition = tableParam.getCondition();
        if(StringUtils.isBlank(condition)){
            sql.append(" 1=1");
        }else{
            sql.append(" ").append(condition);
        }

        try {
            loadData(source, srcTable, sql.toString());
        } catch (Exception e) {
            throw new RuntimeException("["+tableParam.getTableName()+"]源数据加载异常", e);
        }
        try {
            loadData(target, tarTable, sql.toString());
        } catch (Exception e) {
            throw new RuntimeException("["+tableParam.getTableName()+"]目标数据加载异常", e);
        }
        /**
         * 以源数据为基础，不存在的insert，存在的update，多余的delete
         */
        List<String> sqlList = tarTable.compareToSql(srcTable, tableParam.isDelete());
        if(sqlList != null && sqlList.size()>0){
            sqlList.stream().forEach(reporter::appendReport);
        }
    }

    /**
     * 加载数据
     * @param db
     * @param tableData
     * @param sql
     * @throws Exception
     */
    private void loadData(DB db, TableData tableData, String sql) throws Exception{
        try(Statement stm = db.getConn().createStatement()){
            ResultSet rs = stm.executeQuery(sql);
            while(rs.next()){
                resultSetToRowData(tableData, rs);
            }
        }catch (Exception e){
            throw new Exception("["+tableData.getTableName()+"]错误：", e);
        }
    }

    /**
     * 结果集转RowData
     * @param tableData
     * @param rs
     * @throws SQLException
     */
    private void resultSetToRowData(TableData tableData, ResultSet rs) throws SQLException {
        try{
            Map<String, Column> columnMap = tableData.getColumnMap();
            RowData rowData = new RowData();
            for(Column column : columnMap.values()){
                String colName = column.getName();
                String colType = column.getType();
                if(colType.equals("BLOB")){
                    Blob blobData = rs.getBlob(colName);
                    if(blobData != null){
                        byte[] blobBytes = blobData.getBytes(1, (int) blobData.length());
                        String hexString = StrUtils.bytesToHex(blobBytes);
                        rowData.putString(colName, "0x"+hexString);
                    }
                }else{
                    Object value = rs.getObject(colName);
                    if(value == null){
                        continue;
                    }
                    if (colType.indexOf("CHAR") != -1) {
                        String strValue = (String) value;
                        strValue = strValue.replace("'", "''");
                        strValue = strValue.replace("\\", "\\\\");
                        rowData.putString(colName, strValue);
                    }else if(colType.indexOf("TEXT") != -1) {
                        String strValue = (String) value;
                        strValue = strValue.replace("'", "''");
                        rowData.putString(colName, strValue);
                    }else if (colType.startsWith("DATE") || colType.startsWith("TIME")) {
                        rowData.putString(colName, value.toString());
                    } else {
                        rowData.putObject(colName, value);
                    }
                }
            }
            tableData.addRowData(rowData);
        }catch (Exception e){
            throw e;
        }
    }

    /**
     *
     * @param dto
     * @return
     */
    private TableData initTableData(TableParam dto) {
        Table table = source.getTable(dto.getTableName());
        Map<String, Column> columnMap = table.getColumns();
        //
        TableData tableData = new TableData();
        tableData.setTableName(dto.getTableName());
        // 列
        List<String> columnList = new ArrayList<>();
        String columns = dto.getColumns();
        if(StringUtils.isBlank(columns) || "*".equals(columns)){
            columnList = columnMap.keySet().stream().collect(Collectors.toList());
        }else{
            List<String> selColList = Arrays.asList(columns.split(",")).stream().map(String::trim).collect(Collectors.toList());
            columnList = columnMap.keySet().stream().filter(selColList::contains).collect(Collectors.toList());
        }
        columnList.stream().forEach(col->{
            Column column = columnMap.get(col);
            tableData.addColumn(column, false);
        });
        // 主键
        String primaryKey = dto.getPrimaryKey();
        if(StringUtils.isBlank(primaryKey)){
            Map<String, Index> idxMap = table.getIndexes();
            Optional<Index> first = idxMap.values().stream().filter(idx -> "0".equals(idx.getNotUnique())).findFirst();
            if(first.isPresent()){
                tableData.setPrimaryColumns(first.get().getColumns());
            }
        }else{
            tableData.setPrimaryColumns(Arrays.asList(primaryKey.split(",")));
        }
        if(tableData.getPrimaryColumns() == null || tableData.getPrimaryColumns().size() == 0){
            throw new RuntimeException("表["+dto.getTableName()+"]主键为空，无法比对数据");
        }
        return tableData;
    }

    public CompareReporter getReporter() {
        return reporter;
    }

    public void setReporter(CompareReporter reporter) {
        this.reporter = reporter;
    }

    public DB getSource() {
        return source;
    }

    public void setSource(DB source) {
        this.source = source;
    }

    public DB getTarget() {
        return target;
    }

    public void setTarget(DB target) {
        this.target = target;
    }

    public List<TableParam> getToCompareList() {
        return toCompareList;
    }

    public void setToCompareList(List<TableParam> toCompareList) {
        this.toCompareList = toCompareList;
    }

}
